import datetime
import shutil
import sqlite3

import openpyxl
from openpyxl.utils import get_column_letter
from tqdm import tqdm
import pickle
import win32com.client

import tools_pmc
from tools_pmc import (fill_blue, table_array_wlxxb,
                       today_str1, detect_date, alignment_center, lj_mc, yesterday_str1
                       )

lj_JH0020_today = "//192.168.70.101/19计划管理部/01.计划/5，物控管理/3，物料状态管理/data/" + today_str1 + "/JH0020.xlsx"
wb_JH0020 = openpyxl.load_workbook(lj_JH0020_today)  # Material control report forms 物控报表
ws_JH0020 = wb_JH0020.worksheets[0]  # 今日自定义物料参数表


# noinspection PyPep8Naming
# 格式切换
def Format_Conversion(yorn: bool):
    if yorn:
        wb_Mcreforms = openpyxl.workbook.Workbook()
        ws_Mcreforms = wb_Mcreforms.worksheets[0]
        table_array_ws_JH0020 = list(ws_JH0020['A1':'BJ' + str(ws_JH0020.max_row)])
        for i in tqdm(table_array_ws_JH0020):
            for j in range(0, 62):
                ws_Mcreforms.cell(table_array_ws_JH0020.index(i) + 1, j + 1).value = i[j].value
        return wb_Mcreforms, ws_Mcreforms
    else:
        return wb_JH0020, ws_JH0020


def vlookup_wlxxb(lookup_value: str, table_array: list) -> list:
    for row in table_array:
        if str(row[0].value) == str(lookup_value):
            return row[14].value  # 可用数量


# noinspection PyTypeChecker
# 参数修补
def Preprocessing1(wb: openpyxl.workbook.workbook.Workbook):
    ws = wb.worksheets[0]

    # dict_col_y = {"最低存量（原材料）": None,
    #               "365天消耗量": None,
    #               "良品数量": None
    #               }
    # for i in range(1, ws.max_column + 1):
    #     if ws.cell(1, i).value in dict_col_y.keys():
    #         dict_col_y[ws.cell(1, i).value] = i

    dict_col_y = dict_colf(wb)

    ws.insert_cols(dict_col_y["365天消耗量"] + 1, 2)
    ws.cell(1, dict_col_y["365天消耗量"] + 1).value = "可用量预计消耗周期"
    ws.cell(1, dict_col_y["365天消耗量"] + 2).value = "总备货量预计消耗周期"
    ws.insert_cols(dict_col_y["最低存量（原材料）"] + 1)
    ws.cell(1, dict_col_y["最低存量（原材料）"] + 1).value = "工销比"
    ws.cell(1, dict_col_y["良品数量"]).value = "可用数量"


    # dict_col_y = {"指定结束量": None,
    #               "良品数量": None,
    #               "物料新建日期": None,
    #               "最后请购日期": None,
    #               "最后厂内领料出库日期": None,
    #               "最低存量（原材料）": None,
    #               "销货总量": None,
    #               "工单总使用量": None,
    #               "可用量预计消耗周期": None,
    #               "365天消耗量": None,
    #               "可用数量": None,
    #               "工销比": None,
    #               "在途数量": None,
    #               "在检数量": None,
    #               "已请未购数量": None,
    #               "总备货量预计消耗周期": None,
    #               "在库超90天": None,
    #               "在库超180天": None,
    #               "在库超365天": None,
    #               }
    # for i in range(1, ws.max_column + 1):
    #     if ws.cell(1, i).value in dict_col_y.keys():
    #         dict_col_y[ws.cell(1, i).value] = i

    dict_col_y = dict_colf(wb)

    for i in range(2, ws.max_row + 1):
        ws.cell(i, dict_col_y["在库超90天"]).value = min(ws.cell(i, dict_col_y["可用数量"]).value,
                                                         ws.cell(i, dict_col_y["在库超90天"]).value)
        ws.cell(i, dict_col_y["在库超180天"]).value = min(ws.cell(i, dict_col_y["可用数量"]).value,
                                                          ws.cell(i, dict_col_y["在库超180天"]).value)
        ws.cell(i, dict_col_y["在库超365天"]).value = min(ws.cell(i, dict_col_y["可用数量"]).value,
                                                          ws.cell(i, dict_col_y["在库超365天"]).value)
        ws.cell(i, dict_col_y["在途数量"]).value = ws.cell(i, dict_col_y["在途数量"]).value - ws.cell(i, dict_col_y[
            "在检数量"]).value
        # 日期标准化

        if ws.cell(i, dict_col_y["品号"]).value == "302010120013":
            ws.cell(i, dict_col_y["物料新建日期"]).value = "20230112"
        if len(ws.cell(i, dict_col_y["物料新建日期"]).value) == 6:
            temp = "20" + ws.cell(i, dict_col_y["物料新建日期"]).value
        else:
            temp = ws.cell(i, dict_col_y["物料新建日期"]).value.replace('建号', '')
        ws.cell(i, dict_col_y["物料新建日期"]).value = detect_date(temp, 3)  # 统一日期格式
        ws.cell(i, dict_col_y["物料新建日期"]).number_format = 'yyyy/mm/dd'
        for j in [dict_col_y["最后请购日期"], dict_col_y["最后厂内领料出库日期"]]:
            if ws.cell(i, j).value is not None:
                ws.cell(i, j).value = detect_date(ws.cell(i, j).value, 3)  # 统一日期格式
                ws.cell(i, j).number_format = 'yyyy/mm/dd'

        ws.cell(i, dict_col_y["可用数量"]).value = vlookup_wlxxb(ws.cell(i, 1).value, table_array_wlxxb)

        try:
            ws.cell(i, dict_col_y["工销比"]).value = ws.cell(i, dict_col_y["工单总使用量"]).value / ws.cell(i,
                                                                                                            dict_col_y[
                                                                                                                "销货总量"]).value
        except ZeroDivisionError:
            ws.cell(i, dict_col_y["工销比"]).value = 99

        if ws.cell(i, dict_col_y["可用数量"]).value > 0:
            if ws.cell(i, dict_col_y["365天消耗量"]).value == 0:
                ws.cell(i, dict_col_y["可用量预计消耗周期"]).value = 999
            else:
                ws.cell(i, dict_col_y["可用量预计消耗周期"]).value = int(ws.cell(i, dict_col_y["可用数量"]).value /
                                                                         ws.cell(i,
                                                                                 dict_col_y["365天消耗量"]).value * 365)
        else:
            ws.cell(i, dict_col_y["可用量预计消耗周期"]).value = 0

        总备货量 = (ws.cell(i, dict_col_y["可用数量"]).value + ws.cell(i, dict_col_y["在途数量"]).value +
                    ws.cell(i, dict_col_y["在检数量"]).value + ws.cell(i, dict_col_y["已请未购数量"]).value)
        if 总备货量 > 0:
            if ws.cell(i, dict_col_y["365天消耗量"]).value == 0:  # 除数不能为0
                ws.cell(i, dict_col_y["总备货量预计消耗周期"]).value = 999  # 除数为0，预计消耗周期无限大，定为999天
            else:
                ws.cell(i, dict_col_y["总备货量预计消耗周期"]).value = int(
                    总备货量 / ws.cell(i, dict_col_y["365天消耗量"]).value * 365)
        else:
            ws.cell(i, dict_col_y["总备货量预计消耗周期"]).value = 0  # 可用量+在途在检+已请未购预计消耗天数


def vlookup_control_panel(lookup_value: str, table_array: list):
    for row in table_array:
        if str(row[0].value) == str(lookup_value):
            return row


# 拉取物控参数
def Preprocessing2(wb: openpyxl.workbook.workbook.Workbook):
    ws = wb.worksheets[0]

    lj_control_panel = r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\3，物料状态管理\bin\control panel.xlsx"
    wb_control_panel = openpyxl.load_workbook(lj_control_panel)
    ws_control_panel = wb_control_panel.worksheets[0]

    ws.insert_cols(4, 6)
    table_control_panel = list(ws_control_panel['A1':'I' + str(ws_control_panel.max_row)])
    for i in range(1, ws.max_row + 1):
        temp_control_panel = vlookup_control_panel(ws.cell(i, 1).value, table_control_panel)
        if temp_control_panel is None:
            continue
        for j in range(4, 10):
            if j != 7:
                ws.cell(i, j).value = temp_control_panel[j - 1].value
    ws.cell(1, 7).value = "是否呆滞"


def Preprocessing2_1(wb: openpyxl.workbook.workbook.Workbook):
    ws = wb.worksheets[0]

    ws.insert_cols(4, 5)
    ws.cell(1, 4).value = "类别"
    ws.cell(1, 5).value = "管理逻辑"
    ws.cell(1, 6).value = "物料状态"
    ws.cell(1, 7).value = "是否呆滞"
    ws.cell(1, 8).value = "历史记录"

    dict_col_y = dict_colf(wb)

    # 连接到SQLite数据库
    conn = sqlite3.connect(r"\\192.168.70.101\19计划管理部\01.计划\1，数据处理工具\file\config\IK-PMC-MC.db")
    cursor = conn.cursor()
    for i in range(2, ws.max_row + 1):
        cursor.execute("SELECT  * FROM 物控信息1 WHERE 品号 =" + str(ws.cell(i, dict_col_y["品号"]).value))
        rows1 = cursor.fetchone()

        ws.cell(i, dict_col_y["历史记录"]).value = tools_pmc.物控信息拉取_sqlite数据库版(
            ws.cell(i, dict_col_y["品号"]).value, cursor)

        if rows1 is None:
            continue
        if rows1[3] == "专用逻辑":
            ws.cell(i, dict_col_y["类别"]).value = rows1[2]
            ws.cell(i, dict_col_y["管理逻辑"]).value = rows1[3]
            ws.cell(i, dict_col_y["物料状态"]).value = rows1[4]
            ws.cell(i, dict_col_y["是否呆滞"]).value = rows1[5]



# 新物料状态判定、常规逻辑物料状体判定
def State_determination(wb: openpyxl.workbook.workbook.Workbook):
    ws = wb.worksheets[0]

    with open(r"\\192.168.70.101\19计划管理部\01.计划\1，数据处理工具\file\config\tdl.pickle", "rb") as f:
        tdl_list = pickle.load(f)

    dict_col_y = dict_colf(wb)

    for i in range(2, ws.max_row + 1):  # 新物料状态判定
        if ws.cell(i, dict_col_y["管理逻辑"]).value is None:
            ws.cell(i, dict_col_y["管理逻辑"]).value = "常规逻辑"
            if int(ws.cell(i, 1).value) > 107050010001:
                ws.cell(i, dict_col_y["类别"]).value = "工装&耗材"
            elif int(ws.cell(i, 1).value[0:5]) == 10309 or int(ws.cell(i, 1).value[0:5]) == 10310:
                ws.cell(i, dict_col_y["类别"]).value = "线缆"
            elif int(ws.cell(i, 1).value[0:5]) == 10202:
                ws.cell(i, dict_col_y["类别"]).value = "镜头"
            elif int(ws.cell(i, 1).value[0:5]) == 10314:
                ws.cell(i, dict_col_y["类别"]).value = "相机配件"
            elif int(ws.cell(i, 1).value[0:5]) == 10206 or int(ws.cell(i, 1).value[0:5]) == 10207:
                ws.cell(i, dict_col_y["类别"]).value = "光源"
            else:
                ws.cell(i, dict_col_y["类别"]).value = "原材料"

    for i in range(2, ws.max_row + 1):
        if ws.cell(i, dict_col_y["类别"]).value == "原材料" and ws.cell(i, dict_col_y["管理逻辑"]).value == "常规逻辑":
            if ws.cell(i, dict_col_y["BOM规格引用次数"]).value == 0:
                if ws.cell(i, dict_col_y["工单总使用量"]).value == 0:
                    ws.cell(i, dict_col_y["物料状态"]).value = "未启用"
                else:
                    ws.cell(i, dict_col_y["物料状态"]).value = "停用"
            elif ws.cell(i, dict_col_y["Y状态BOM规格引用次数"]).value == 0:
                ws.cell(i, dict_col_y["物料状态"]).value = "停用"
            elif ws.cell(i, dict_col_y["365天消耗量"]).value == 0:
                ws.cell(i, dict_col_y["物料状态"]).value = "正常C"
            elif ws.cell(i, dict_col_y["固定前置天数"]).value <= 90:
                if ws.cell(i, dict_col_y["总备货量预计消耗周期"]).value >= 120:
                    ws.cell(i, dict_col_y["物料状态"]).value = "正常B"
                else:
                    ws.cell(i, dict_col_y["物料状态"]).value = "正常A"
            else:
                if ws.cell(i, dict_col_y["总备货量预计消耗周期"]).value >= ws.cell(i, dict_col_y["固定前置天数"]).value - 2:
                    ws.cell(i, dict_col_y["物料状态"]).value = "正常B"
                else:
                    ws.cell(i, dict_col_y["物料状态"]).value = "正常A"
        if ws.cell(i, dict_col_y["物料状态"]).value == "未启用" or ws.cell(i, dict_col_y["物料状态"]).value == "停用":
            if ws.cell(i, 1).value in tdl_list and ws.cell(i, dict_col_y["可用数量"]).value != 0:
                ws.cell(i, dict_col_y["物料状态"]).value = "仅为替代料"

    for i in range(2, ws.max_row + 1):  # 呆滞判定
        if ws.cell(i, dict_col_y["可用数量"]).value > 0:
            if ws.cell(i, dict_col_y["物料状态"]).value == "停用" or ws.cell(i, dict_col_y["物料状态"]).value == "未启用":
                ws.cell(i, dict_col_y["是否呆滞"]).value = "呆滞"
            elif ws.cell(i, dict_col_y["固定前置天数"]).value <= 90 and ws.cell(i, dict_col_y[
                "可用量预计消耗周期"]).value > 120:
                if ws.cell(i, dict_col_y["物料状态"]).value is not None:  # 原材料
                    ws.cell(i, dict_col_y["是否呆滞"]).value = "有呆滞风险"
                else:  # 相机配件
                    ws.cell(i, dict_col_y["是否呆滞"]).value = "呆滞"
            elif (ws.cell(i, dict_col_y["固定前置天数"]).value > 90 and
                  ws.cell(i, dict_col_y["可用量预计消耗周期"]).value > ws.cell(i, dict_col_y["固定前置天数"]).value - 2):
                if ws.cell(i, dict_col_y["物料状态"]).value is not None:  # 原材料
                    ws.cell(i, dict_col_y["是否呆滞"]).value = "有呆滞风险"
                else:  # 相机配件
                    ws.cell(i, dict_col_y["是否呆滞"]).value = "呆滞"


def Adjust_Format(wb: openpyxl.workbook.workbook.Workbook):
    ws = wb.worksheets[0]
    dict_colwidth = {"品号": 14,
                     "品名": 22,
                     "规格": 22,
                     "物料新建日期": 12,
                     "最后请购日期": 12,
                     "最后OA流水号": 12,
                     "最后备注": 20,
                     "最后备料原因": 20,
                     "最后厂内领料出库日期": 14,
                     "最后厂内领料使用对象": 16,
                     "是否呆滞": 10,
                     "物料状态": 9
                     }
    for i in range(1, ws.max_column + 1):
        if ws.cell(1, i).value in dict_colwidth.keys():
            ws.column_dimensions[get_column_letter(i)].width = dict_colwidth[ws.cell(1, i).value]
        else:
            ws.column_dimensions[get_column_letter(i)].width = 8.5

    dict_del = {"30天进货量": None,
                "90天进货量": None,
                "180天进货量": None,
                "2022工单使用量": None,
                "2023工单使用量": None,
                # "30天研发使用量": None,
                # "90天研发使用量": None,
                "30天生产使用量": None,
                "90天生产使用量": None,
                "180天生产使用量": None,
                # "30天销货量": None,
                # "90天销货量": None,
                "180天销货量": None,
                "在库超90天": None,
                "在库超180天": None,
                # "领用倍量": None,
                # "品号属性": None,
                "指定结束量": None,
                # "计划请购量": None,
                # "非计划请购量": None,
                # "非原材料仓请购量": None,
                "工销比": None,
                "库存数量":None,
                "欠料数量": None,
                }

    for i in range(1, ws.max_column + 1):
        if ws.cell(1, i).value in dict_del.keys():
            dict_del[ws.cell(1, i).value] = i
    print(dict_del)
    temp = list(dict_del.values())
    for i in temp:
        ws.column_dimensions[get_column_letter(i)].width = 0.0001

    # noinspection PyTypeChecker
    ws.row_dimensions[1].height = 40
    for i in range(1, ws.max_column + 1):
        ws.cell(1, i).alignment = alignment_center
        ws.cell(1, i).fill = fill_blue

    ws.freeze_panes = 'D2'
    range_active = "A1:" + get_column_letter(ws.max_column) + str(ws.max_row)
    ws.auto_filter.ref = range_active


def temp_Control_panel(wb: openpyxl.workbook.workbook.Workbook):
    ws = wb.worksheets[0]

    dict_col_y = dict_colf(wb)
    conn = sqlite3.connect(r"\\192.168.70.101\19计划管理部\01.计划\1，数据处理工具\file\config\IK-PMC-MC.db")
    cursor = conn.cursor()

    for i in range(2, ws.max_row + 1):
        cursor.execute('''
            UPDATE 物控信息1
            SET 类别 = ?, 管理逻辑 = ?, 物料状态 = ?, 是否呆滞 = ?
            WHERE 品号 = ?
        ''', (ws.cell(i,dict_col_y["类别"]).value,
              ws.cell(i,dict_col_y["管理逻辑"]).value,
              ws.cell(i, dict_col_y["物料状态"]).value,
              ws.cell(i, dict_col_y["是否呆滞"]).value,
              ws.cell(i,dict_col_y["品号"]).value))
        conn.commit()  # 提交事务

        # 关闭数据库连接
    conn.close()


def change():  #
    lj_mc_change = r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\3，物料状态管理\bin\自定义报表数据变动.txt"
    f = open(lj_mc_change)  # 返回一个文件对象
    line = f.readline()  # 调用文件的 readline()方法
    list1 = []
    while line:
        if line == '\n':
            list1.append([])
        else:
            for i in ["\"", "\n", "变更人：", "变更前状态：", "变更后状态：", "变更意见：", "修改表格："]:
                if i in line:
                    line = line.replace(i, "")
            list1[len(list1) - 1].append(line)
        line = f.readline()
    f.close()
    with open(lj_mc_change, "w") as file:
        file.write("")

    wb = openpyxl.load_workbook(
        r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\3，物料状态管理\态状态更台账.xlsx")
    ws = wb.worksheets[0]
    a = ws.max_row + 1
    for i in list1:
        ws.cell(a, 1).value = i[3]
        ws.cell(a, 2).value = i[4]
        ws.cell(a, 3).value = i[5]
        ws.cell(a, 4).value = i[1]
        ws.cell(a, 5).value = i[6]
        ws.cell(a, 6).value = i[7]
        ws.cell(a, 7).value = i[2]
        ws.cell(a, 8).value = i[8]
        a += 1
    wb.save(r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\3，物料状态管理\态状态更台账.xlsx")
    if len(list1) != 0:
        print("物料状态变更%d", len(list1))


def temp_tool(wb: openpyxl.workbook.workbook.Workbook):
    ws = wb.worksheets[0]
    dict_col_y = {"类别": None,
                  }
    for i in range(1, ws.max_column + 1):
        if ws.cell(1, i).value in dict_col_y.keys():
            dict_col_y[ws.cell(1, i).value] = i

    data = []
    for i in range(2, ws.max_row + 1):
        if ws.cell(i, dict_col_y["类别"]).value == "线缆":
            data.append(ws.cell(i, 1).value)
    with open(r"\\192.168.70.101\19计划管理部\01.计划\1，数据处理工具\file\config\line.pickle", "wb") as f:
        pickle.dump(data, f)


def help_log(wb: openpyxl.workbook.workbook.Workbook):
    ws = wb.worksheets[0]
    ws.title = "物控报表" + today_str1
    wb.create_sheet("帮助")
    ws = wb["帮助"]


def 说明_变更记录():
    excel = win32com.client.gencache.EnsureDispatch('Excel.Application')
    excel.Visible = False
    excel.DisplayAlerts = False

    wb_1 = excel.Workbooks.Open(
        r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\3，物料状态管理\开发文档\逻辑说明.xlsx")
    ws_1 = wb_1.Worksheets(1)
    ws_2 = wb_1.Worksheets(2)
    ws_3 = wb_1.Worksheets(3)
    wb = excel.Workbooks.Open(lj_mc)
    ws = wb.Worksheets(1)
    ws_3.Copy(After=ws)
    ws_2.Copy(After=ws)
    ws_1.Copy(After=ws)

    ws.Activate()
    wb.SaveAs(lj_mc)
    wb_1.Close()
    wb.Close()


def dict_colf(wb: openpyxl.workbook.workbook.Workbook):
    dict1 = {}
    ws = wb.worksheets[0]
    for i in range(1, ws.max_column + 1):
        dict1[ws.cell(1, i).value] = i
    return dict1


def 配件类物料90天库龄新增(wb: openpyxl.workbook.workbook.Workbook):
    ws = wb.worksheets[0]

    wb_y = openpyxl.load_workbook(
        "//192.168.70.101/19计划管理部/01.计划/5，物控管理/3，物料状态管理/data/" + tools_pmc.yesterday_str1 + "/物控报表" + tools_pmc.yesterday_str1 + ".xlsx")
    ws_y = wb_y.worksheets[0]

    wb_异常 = openpyxl.load_workbook(
        r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\3，物料状态管理\异常监控台账.xlsx")
    ws_配件90天库存新增 = wb_异常["配件90天库存新增"]
    if ws_配件90天库存新增.cell(ws_配件90天库存新增.max_row, 1).value.date() == datetime.date.today():
        return

    dict_col_y = dict_colf(wb_y)
    dict_col = dict_colf(wb)
    dict_xlandpj = {}
    for i in range(2, ws_y.max_row + 1):
        if (ws_y.cell(i, dict_col_y["类别"]).value == "相机配件"
                or ws_y.cell(i, dict_col_y["类别"]).value == "镜头"
                or ws_y.cell(i, dict_col_y["类别"]).value == "光源"
                or ws_y.cell(i, dict_col_y["类别"]).value == "线缆"):
            dict_xlandpj[ws_y.cell(i, dict_col_y["品号"]).value] = ws_y.cell(i, dict_col_y["在库超90天"]).value

    a = ws_配件90天库存新增.max_row + 1
    for i in range(2, ws.max_row + 1):
        if ws.cell(i, dict_col["品号"]).value in dict_xlandpj.keys() \
                and ws.cell(i, dict_col["在库超90天"]).value > dict_xlandpj[ws.cell(i, dict_col_y["品号"]).value] \
                and ws.cell(i, dict_col["在库超90天"]).value - dict_xlandpj[ws.cell(i, dict_col_y["品号"]).value] > 5:
            list1 = [datetime.date.today(),
                     ws.cell(i, dict_col["品号"]).value,
                     ws.cell(i, dict_col["品名"]).value,
                     ws.cell(i, dict_col["规格"]).value,
                     dict_xlandpj[ws.cell(i, dict_col_y["品号"]).value],
                     ws.cell(i, dict_col["在库超90天"]).value,
                     ws.cell(i, dict_col["在库超90天"]).value - dict_xlandpj[ws.cell(i, dict_col_y["品号"]).value]
                     ]
            for j in range(len(list1)):
                ws_配件90天库存新增.cell(a, j + 1).value = list1[j]
            ws_配件90天库存新增.cell(a, 1).number_format = 'yyyy/mm/dd'
            a += 1

    wb_异常.save(r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\3，物料状态管理\异常监控台账.xlsx")


def 未请购采购量异常新增():
    wb_异常 = openpyxl.load_workbook(
        r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\3，物料状态管理\异常监控台账.xlsx")
    ws_未请购采购量新增 = wb_异常["未请购采购量新增"]
    if ws_未请购采购量新增.cell(ws_未请购采购量新增.max_row, 1).value.date() == datetime.date.today():
        return

    wb_wlxxb_y = openpyxl.load_workbook(tools_pmc.lj_wlxxb_y)
    ws_wlxxb_y = wb_wlxxb_y.worksheets[0]
    dict_列标_y = {}
    for i in range(1, ws_wlxxb_y.max_column + 1):
        dict_列标_y[ws_wlxxb_y.cell(2, i).value] = i
    dict_col_y = {}
    for i in range(2, ws_wlxxb_y.max_row + 1):
        dict_col_y[str(ws_wlxxb_y.cell(i, dict_列标_y["品号"]).value)] = ws_wlxxb_y.cell(i, dict_列标_y[
            "未请购采购量"]).value

    wb_wlxxb = openpyxl.load_workbook(tools_pmc.lj_wlxxb)
    ws_wlxxb = wb_wlxxb.worksheets[0]
    dict_列标 = {}
    for i in range(1, ws_wlxxb.max_column + 1):
        dict_列标[ws_wlxxb.cell(2, i).value] = i
    dict_col = {}
    for i in range(2, ws_wlxxb.max_row + 1):
        dict_col[str(ws_wlxxb.cell(i, dict_列标["品号"]).value)] = ws_wlxxb.cell(i, dict_列标["未请购采购量"]).value

    a = ws_未请购采购量新增.max_row + 1

    for i in dict_col:
        if i in dict_col_y and dict_col[i] > dict_col_y[i]:
            list1 = [datetime.date.today(),
                     i,
                     tools_pmc.vlookup(i, tools_pmc.table_array_wlxxb, 2),
                     tools_pmc.vlookup(i, tools_pmc.table_array_wlxxb, 3),
                     dict_col_y[i],
                     dict_col[i],
                     dict_col[i] - dict_col_y[i]
                     ]
            for j in range(len(list1)):
                ws_未请购采购量新增.cell(a, j + 1).value = list1[j]
            ws_未请购采购量新增.cell(a, 1).number_format = 'yyyy/mm/dd'
            a += 1

    wb_异常.save(r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\3，物料状态管理\异常监控台账.xlsx")


def 成品状态二次判定():
    try:
        shutil.copy(
            "//192.168.70.101/19计划管理部/01.计划/5，物控管理/3，物料状态管理/成品状态二次判定表.xlsx",
            "//192.168.70.101/19计划管理部/01.计划/5，物控管理/3，物料状态管理/data/" + yesterday_str1)
    except FileNotFoundError:
        pass

    wb_原 = openpyxl.load_workbook(
        r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\3，物料状态管理\成品状态二次判定表.xlsx")
    ws_原 = wb_原.worksheets[0]
    dict_原 = {}
    for i in range(2, ws_原.max_row + 1):
        dict_原[ws_原.cell(i, 2).value] = [ws_原.cell(i, 3).value, ws_原.cell(i, 4).value, ws_原.cell(i, 5).value]

    wb = openpyxl.Workbook()
    ws = wb.worksheets[0]
    dict_bom_all = tools_pmc.Y状态BOM尾阶用量_pro()
    list_无效成品品号 = []
    a = 2
    for i in dict_bom_all:
        ws.cell(a, 1).value = i
        if len(dict_bom_all[i][0]) > 1:
            ws.merge_cells("A" + str(a) + ":A" + str(a + len(dict_bom_all[i][0]) - 1))
            ws.cell(a, 1).alignment = tools_pmc.alignment_lcf
        for j in dict_bom_all[i][0]:
            ws.cell(a, 2).value = j
            if j in dict_原.keys():
                ws.cell(a, 3).value = dict_原[j][0]
                ws.cell(a, 4).value = dict_原[j][1]
                if dict_原[j][0] == "×":
                    list_无效成品品号.append(j)
            else:
                ws.cell(a, 4).value = str(datetime.date.today()) + "新增"
            a += 1

    list1 = ["规格", "品号", "当前状态", "备注1", "备注2"]
    list2 = [18, 14, 10, 30, 30]
    for i in range(len(list1)):
        ws.cell(1, i + 1).value = list1[i]
        ws.cell(1, i + 1).fill = tools_pmc.fill_blue
        ws.column_dimensions[get_column_letter(i + 1)].width = list2[i]
    ws.freeze_panes = 'A2'
    ws.auto_filter.ref = "A1:E" + str(ws.max_row)

    wb.save(r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\3，物料状态管理\成品状态二次判定表.xlsx")

    return list_无效成品品号, dict_bom_all

# def State_Change(wb: openpyxl.workbook.workbook.Workbook):
#     ws = wb.worksheets[0]
#     dict1 = {""
#
#
#
#
#     list_change = change()
#     for i in list_change:
#
#
#
#     for i in range(2,ws.max_row+1):
#         if
